USE SAS
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetAccountActivities')
	BEGIN
		DROP  Procedure  dbo.GetAccountActivities
	END

GO

CREATE Procedure dbo.GetAccountActivities
	@accountGUID		VARCHAR(100)
,	@userId				VARCHAR(10) = '0'
AS
BEGIN
	
	;WITH A AS (
		SELECT 
			a.ActivityTaskID	AS ActivityID
		,	a.[Description]		AS ActivityName
		,	t.TypeDesc			as ActivityType
		,	s.StatusDesc		as ActivityStatus
		,	a.StartDate			as StartDate
		,	a.ActivityTaskGUID	AS PKID
		FROM TR_ACTIVITYTASK as a WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			on r.SourceGUID = a.ActivityTaskGUID
		LEFT OUTER JOIN TR_STATUS as s WITH (NOLOCK)
			on s.Status = a.Status
		LEFT OUTER JOIN TR_ACTIVITYTYPE as t WITH (NOLOCK)
			on t.ActivityType = a.ActivityType
		WHERE 
			r.Partner2 = @userID
		AND r.RelationShipType = 3 -- OWNER
		AND	ISNULL(a.IsTask,0) = 0
	), B AS (
		-- GET ACCOUNTS FOR THE ACTIVITIES
		SELECT
			act.PartnerName		as 	AccountName
		,	a.ActivityID
		FROM A 
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.SourceGUID = a.PKID
		INNER JOIN TR_PARTNER as act WITH (NOLOCK)
			on act.PartnerNo = r.Partner2
		WHERE
			r.RelationShiptype = 9 -- 9: Activity Partner
		AND act.PartnerGUID = @accountGUID
	), Final AS (
		SELECT
			a.ActivityName		AS [Activity Name]
		,	a.ActivityType		as [Activity Type]
		,	a.ActivityStatus	as [Status]
		,	a.StartDate			as [Start Date]
		,	a.PKID				AS [PKID]
--		,	a.ActivityID		AS [Activity ID]
--		,	b.AccountName		AS [Account Name]
--		,	c.ContactName		as [Contact]
		FROM A
		INNER JOIN B
			on B.ActivityID = A.ActivityID
	)

	SELECT * FROM Final
	ORDER BY [Start Date] DESC
END
GO

-- exec GetAccountActivities

-- select * from tr_partner order by 1
